﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace Clinica_Frba
{
    public partial class Estad : Form
    {
        Conexion sql = Conexion.getInstance();
        

        public Estad()
        {
            InitializeComponent();
        }

        private void Estad_Load(object sender, EventArgs e)
        {


        }

        public void loadEstad(int numListado, string anio, int semestre)
        {
            string qSel = "", qSel2 = "", qFrom = "", qFrom2 = "", qWhere = "", qWhere2 = "", qGroup = "", qGroup2 = "", query = "";
            bool multiple = false;
            SqlDataAdapter adapter;
            DataTable table = new DataTable();
            int sem, cantMeses;

            if (semestre == 1)
            {
                sem = 1;
                cantMeses = 6;
            }
            else
            {
                sem = 7;
                cantMeses = 12;
            }

            switch (numListado)
            {
                case 1:
                    {
                        qSel = "SELECT TOP 5 DATENAME(MONTH,Tu.Turno_Fecha) Mes, Es.Esp_Descripcion Especialidad, COUNT(*) as 'Cantidad de Cancelaciones' , COUNT(pa.Id_Paciente) 'Cancelados Por Pacientes', COUNT(me.id_medico) 'Cancelados por Medicos' ";
                        qFrom = "FROM b_sharp.Especialidades AS Es " +
                                "JOIN b_sharp.Turnos as Tu on Tu.Medico_Especialidad = Es.Cod_Especialidad " +
                                "JOIN b_sharp.Cancelaciones as Ca on Ca.Turno_Numero = Tu.Turno_Numero " +
                                "LEFT JOIN b_sharp.Pacientes as pa on Tu.Paciente_ID = pa.Id_Paciente and Ca.Tipo_Cancelacion = 1 " +
                                "LEFT JOIN b_sharp.Medicos as me on me.id_medico = Tu.Medico_ID and Ca.Tipo_Cancelacion = 2 ";
                        qWhere = "WHERE YEAR(Tu.Turno_Fecha) = '" + anio + "' " +
                                 "AND MONTH(Tu.Turno_Fecha) = '";
                        qGroup = "GROUP BY DATENAME(MONTH,Tu.Turno_Fecha),Es.Esp_Descripcion " +
                                 "ORDER BY 3 desc ";


                        break;
                    }
                case 2:
                    {
                        /*qSel = "SELECT TOP 5 BF.Id_Paciente_Compra AS ID_Paciente, Pa.Paciente_Apellido + ', ' + Pa.Paciente_Nombre as NyA, COUNT (*) as 'Cantidad de Bonos Vencidos' ";
                        qFrom = "FROM b_sharp.BonoFarmacia AS BF " +
                                "JOIN b_sharp.Pacientes AS Pa on Pa.Id_Paciente = BF.Id_Paciente_Compra ";
                        qWhere = "WHERE YEAR(BF.BonoFarmacia_FechaVencimiento) = '" + anio + "' AND BF.Id_Paciente_Util is null AND MONTH(BF.BonoFarmacia_FechaVencimiento) < '";
                        qGroup = "GROUP BY BF.Id_Paciente_Compra, Pa.Paciente_Apellido, Pa.Paciente_Nombre UNION ";*/

                        qSel = "SELECT TOP 5 DATENAME(MONTH,BF.BonoFarmacia_FechaVencimiento) Mes, BF.Id_Paciente_Compra AS ID_Paciente, Pa.Paciente_Apellido + ', ' + Pa.Paciente_Nombre as NyA, COUNT (*) as 'Cantidad de Bonos Vencidos' ";
                        qFrom = "FROM b_sharp.BonoFarmacia AS BF " +
                                 "JOIN b_sharp.Pacientes AS Pa on Pa.Id_Paciente = BF.Id_Paciente_Compra ";
                        qWhere = "WHERE YEAR(BF.BonoFarmacia_FechaVencimiento) = '" + anio + "' " +
                                  "AND BF.Id_Paciente_Util is null " +
                                  "AND MONTH(BF.BonoFarmacia_FechaVencimiento) = '";
                        qGroup = "GROUP BY DATENAME(MONTH,BF.BonoFarmacia_FechaVencimiento), Id_Paciente_Compra, Pa.Paciente_Apellido, Pa.Paciente_Nombre " +
                                  "ORDER BY 4 DESC ";
                        //multiple = true;


                        titulo.Text = "Bonos Farmacia vencidos por Afiliado";


                        break;
                    }
                case 3:
                    {
                        qSel = "SELECT TOP 5 DATENAME(MONTH,Re.Fecha_Prescripcion) Mes, Es.Esp_Descripcion AS 'Descripcion de Especialidad', COUNT(BonoF_Num) AS 'Cantidad de Bono Farmacia Recetados' ";
                        qFrom = "FROM b_sharp.Especialidades as Es " +
                                "JOIN b_sharp.EspecialidadMedico as EsMe on EsMe.Cod_Especialidad = Es.Cod_Especialidad " +
                                "JOIN b_sharp.Medicos as Me on Me.id_medico = EsMe.id_medico " +
                                "JOIN b_sharp.Consultas as Co on Me.id_medico = Co.Id_Medico " +
                                "JOIN b_sharp.Recetas as Re on Re.Consulta_Num = Co.Consulta_Numero ";
                        qWhere = "WHERE YEAR(Re.Fecha_Prescripcion) = '" + anio + "' AND MONTH(Re.Fecha_Prescripcion) = '";
                        qGroup = "GROUP BY DATENAME(MONTH,Re.Fecha_Prescripcion), Es.Cod_Especialidad, Es.Esp_Descripcion " +
                                 "ORDER BY 3 DESC ";

                        break;
                    }
                case 4:
                    {
                        qSel = "SELECT TOP 10 DATENAME(MONTH,BC.Fecha_Compra) Mes, Pa.Id_Paciente, COUNT (*) AS 'Cantidad de Afiliados' ";
                        qFrom = "FROM b_sharp.Pacientes as Pa " +
                                "JOIN b_sharp.BonoConsulta as BC on BC.Id_Paciente_Util = Pa.Id_Paciente AND BC.Id_Paciente_Compra <> Pa.Id_Paciente ";
                        qWhere = "WHERE YEAR(BC.Fecha_Compra) = '" + anio + "' " + "AND MONTH(BC.Fecha_Compra) = '";
                        qGroup = "GROUP BY DATENAME(MONTH,BC.Fecha_Compra), Pa.Id_Paciente " + "UNION ";

                        qSel2 = "SELECT DATENAME(MONTH,BF.BonoFarmacia_FechaCompra) Mes, Pa.Id_Paciente, COUNT (*) AS 'Cantidad de Afiliados' ";
                        qFrom2 = "FROM b_sharp.Pacientes as Pa " +
                                "JOIN b_sharp.BonoFarmacia as BF on BF.Id_Paciente_Util = Pa.Id_Paciente AND BF.Id_Paciente_Compra <> Pa.Id_Paciente ";
                        qWhere2 = "WHERE YEAR(BF.BonoFarmacia_FechaCompra) = '" + anio + "' AND MONTH(BF.BonoFarmacia_FechaCompra) = '";
                        qGroup2 = "GROUP BY DATENAME(MONTH,BF.BonoFarmacia_FechaCompra), Pa.Id_Paciente " +
                                "ORDER BY 2 DESC ";


                        multiple = true;



                        break;
                    }
                default:
                    {
                        MessageBox.Show("Error");
                        break;
                    }
            }

            for (int i = sem; i <= cantMeses; i++)
            {              

                if (!multiple)
                {
                    query = qSel + qFrom + qWhere + i + "' " + qGroup + ";";

                }
                else
                {
                    query = qSel + qFrom + qWhere + i + "' " + qGroup + qSel2 + qFrom2 + qWhere2 + i + "' " + qGroup2 + ";";
                }

                
                adapter = sql.execQueryGrid(query);
                adapter.Fill(table);                           

            }

            mes1Grid.DataSource = table;
            mes1Grid.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.None;
            mes1Grid.AutoResizeColumns();
            mes1Grid.AllowUserToResizeColumns = true;
            mes1Grid.AllowUserToOrderColumns = true;
            this.Show();
        }
    }
}
